﻿using Dapper;
using Dapper.Contrib.Extensions;
using EWC.Core.Application.Dtos;
using EWC.Core.DependencyInjection;
using EWC.HIS.Dapper.Data;
using EWC.HIS.Domain.Data.Enums;
using EWC.HIS.Domain.Data.Repositories;
using EWC.HIS.Domain.Entities;
using EWC.HIS.Domain.Repositories;
using EWC.HIS.Domain.ViewManage.Do;
using SqlKata;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace EWC.HIS.Dapper.Repositories
{
    public class AppointmentRegistrationRepository : RepositoryBase<AppointmentRegistration>, IAppointmentRegistrationRepository, IScopedDependency
    {
        public AppointmentRegistrationRepository(IDbContext dbContext) : base(dbContext)
        {
        }

        // 数据库连接字符串属性，默认使用 HIS 数据库
        //protected override EDbConnStr DbConnStr => EDbConnStr.HIS;

        public virtual async Task<AppointmentRegistration> GetAppointmentRegistrationByIdAsync(int id)
        {
            var appointmentRegistration = await GetAsync(id);

            return appointmentRegistration;
        }

        public virtual async Task<AppointmentRegistration> GetAppointmentRegistrationByAsync(string patientName, string medicalCondition)
        {
            var query = new Query(TableName).Select("PatientName", "AppointmentTime", "MedicalCondition", "CreationTime").WhereFalse("Deleted");

            if (!string.IsNullOrEmpty(patientName))
            {
                query = query.WhereContains("PatientName", patientName);
            }

            if (!string.IsNullOrEmpty(medicalCondition))
            {
                query = query.WhereContains("MedicalCondition", medicalCondition);
            }

            var sqlResult = GetSqlResult(query);

            return await GetFirstOrDefaultAsync(sqlResult.Sql, sqlResult.NamedBindings);
        }

        public virtual async Task<IEnumerable<AppointmentRegistration>> GetAllAppointmentRegistrationsAsync()
        {
            var query = new Query(TableName).Select("PatientName", "AppointmentTime", "MedicalCondition", "CreationTime").WhereFalse("Deleted");

            var sqlResult = GetSqlResult(query);
            return await GetListAsync(sqlResult.Sql, sqlResult.NamedBindings);
        }


        public virtual async Task<Tuple<int, IEnumerable<AppointmentRegistration>>> GetPagedAppointmentRegistrations(string patientName, string medicalCondition, int pageIndex, int pageSize, bool useProcedureForAppointmentRegistrationPaged = false)
        {
            using (IDbConnection session = await DbContext.GetConnectionAsync())
            {
                // 检查是否应使用存储过程检索预约信息
                if (useProcedureForAppointmentRegistrationPaged)
                {
                    // 为储存过程添加输入参数
                    DynamicParameters dynamicParameters = new DynamicParameters();

                    dynamicParameters.Add("PatientName", patientName, DbType.String, ParameterDirection.Input);
                    dynamicParameters.Add("MedicalCondition", patientName, DbType.String, ParameterDirection.Input);
                    dynamicParameters.Add("PageIndex", pageIndex, DbType.Int32, ParameterDirection.Input);
                    dynamicParameters.Add("PageSize", pageSize, DbType.Int32, ParameterDirection.Input);
                    dynamicParameters.Add("TotalRecords", 0, DbType.Int32, ParameterDirection.Output);

                    // 调用有返回值的储存过程，并一次性获取结果
                    var result = await session.QueryMultipleAsync("AppointmentRegistrationPaged", dynamicParameters, commandType: CommandType.StoredProcedure);

                    // 将返回的结果读入 List<AppointmentRegistration> 中
                    var customers = (await result.ReadAsync<AppointmentRegistration>()).ToList();

                    // 读取存储过程输出参数，即正确的总记录数
                    var totalRecord = await result.ReadFirstAsync<int>();

                    return new Tuple<int, IEnumerable<AppointmentRegistration>>(totalRecord, customers);
                }
                else  // 否则，使用常规查询来检索客户信息
                {
                    // 获取总记录数的查询对象
                    var totalRecordQuery = new Query(TableName).WhereFalse("Deleted").AsCount();

                    // 获取客户信息的查询对象
                    var customerQuery = new Query(TableName)
                        .Select("Id", "PatientName", "MedicalCondition", "AppointmentTime", "CreationTime")
                        .WhereFalse("Deleted");

                    // 如果需要搜索那么患者名出现在结果之中，添加匹配语句
                    if (!string.IsNullOrWhiteSpace(patientName))
                    {
                        totalRecordQuery.WhereStarts("PatientName", patientName);
                        customerQuery.WhereStarts("PatientName", patientName);
                    }

                    // 如果需要搜索某种医学条件，添加匹配语句
                    if (!string.IsNullOrWhiteSpace(medicalCondition))
                    {
                        totalRecordQuery.WhereStarts("MedicalCondition", medicalCondition);
                        customerQuery.WhereStarts("MedicalCondition", medicalCondition);
                    }

                    // 获取总记录数查询和客户信息查询的 SQL 语句，以及它们的命名绑定参数
                    var totalRecordResult = GetSqlResult(totalRecordQuery);
                    var customerQueryResult = GetSqlResult(customerQuery.OrderByDesc("Id").Limit(pageSize).Offset(pageIndex * pageSize));

                    // 调用多行处理查询，一次性获取客户信息和总记录数
                    var multiResult = await session.QueryMultipleAsync(customerQueryResult.Sql + ";" + totalRecordResult.Sql, customerQueryResult.NamedBindings);
                    var customers = (await multiResult.ReadAsync<AppointmentRegistration>()).ToList();
                    var totalRecord = await multiResult.ReadFirstAsync<int>();

                    return new Tuple<int, IEnumerable<AppointmentRegistration>>(totalRecord, customers);
                }
            }
        }




        //public virtual async Task<Tuple<int, IEnumerable<DepartmentDo>>> GetPagedDepartments(string departmentName, string departmentCategory, int pageIndex, int pageSize)
        //{
        //    using (IDbContext session = await DbContext)
        //    {
        //        // 获取总记录数的查询对象
        //        var totalRecordQuery = new Query("VW_Lis_Dict_Dept").AsCount();

        //        string sql = "SELECT  [科室代码] as DeptCode, [科室名称] as DeptName, [科室类别] as DeptCategory, [HIS中的原始科室代码] as HISOriginalDeptCode, [所属院区代码] as BelongsHospitalDistrictCode, [专业类别] as ProfessionalCategory, [联系电话] as ContactNumber, [上级科室代码] as SuperiorDeptCode, [床位数] as BedNumber FROM [EHIS].[dbo].[VW_Lis_Dict_Dept]";

        //        IEnumerable<DepartmentDo> departments = await session.Connection.QueryAsync<DepartmentDo>(sql);
        //        var list = departments.ToList();

        //        return new Tuple<int, IEnumerable<DepartmentDo>>(1, departments);
        //    }
        //}

        //public virtual async Task<Tuple<int, IEnumerable<AppointmentRegistration>>> GetPagedAppointmentRegistrations(string patientName, string medicalCondition, int pageIndex, int pageSize, bool useProcedureForAppointmentRegistrationPaged = false)
        //{
        //    IDbSession session = await Task.Run(() => DbSession);

        //    IEnumerable<AppointmentRegistration> customers;
        //    int totalRecord;

        //    if (useProcedureForAppointmentRegistrationPaged)
        //    {
        //        DynamicParameters dynamicParameters = new DynamicParameters();

        //        dynamicParameters.Add("PatientName", patientName, DbType.String, ParameterDirection.Input);
        //        dynamicParameters.Add("MedicalCondition", patientName, DbType.String, ParameterDirection.Input);
        //        dynamicParameters.Add("PageIndex", pageIndex, DbType.Int32, ParameterDirection.Input);
        //        dynamicParameters.Add("PageSize", pageSize, DbType.Int32, ParameterDirection.Input);
        //        dynamicParameters.Add("TotalRecords", 0, DbType.Int32, ParameterDirection.Output);

        //        customers = await session.Connection.QueryAsync<AppointmentRegistration>("AppointmentRegistrationPaged", dynamicParameters, commandType: CommandType.StoredProcedure);
        //        totalRecord = dynamicParameters.Get<int>("TotalRecords");
        //        session.Dispose();
        //    }
        //    else
        //    {
        //        Query totalRecordQuery = new Query(TableName).WhereFalse("Deleted").AsCount();
        //        Query customerQuery = new Query(TableName).Select("Id", "PatientName", "MedicalCondition", "AppointmentTime", "CreationTime").WhereFalse("Deleted");

        //        if (!string.IsNullOrEmpty(patientName))
        //        {
        //            totalRecordQuery = totalRecordQuery.WhereStarts("PatientName", patientName);
        //            customerQuery = customerQuery.WhereStarts("PatientName", patientName);
        //        }

        //        if (!string.IsNullOrEmpty(medicalCondition))
        //        {
        //            totalRecordQuery = totalRecordQuery.WhereStarts("MedicalCondition", medicalCondition);
        //            customerQuery = customerQuery.WhereStarts("MedicalCondition", medicalCondition);
        //        }

        //        SqlResult totalRecordResult = GetSqlResult(totalRecordQuery);

        //        customerQuery = customerQuery.OrderByDesc("Id").Limit(pageSize).Offset(pageIndex * pageSize);
        //        SqlResult customerResult = GetSqlResult(customerQuery);

        //        var multi = await session.Connection.QueryMultipleAsync(customerResult.Sql + ";" + totalRecordResult.Sql, customerResult.NamedBindings);
        //        var result = await multi.ReadAsync<AppointmentRegistration>();
        //        customers = result.ToList();
        //        totalRecord = multi.ReadFirst<int>();

        //        session.Dispose();
        //    }

        //    return new Tuple<int, IEnumerable<AppointmentRegistration>>(totalRecord, customers);
        //}

        public virtual async Task<int> InsertAppointmentRegistrationAsync(AppointmentRegistration appointmentRegistration)
        {
            if (appointmentRegistration == null)
                throw new ArgumentNullException(nameof(appointmentRegistration));
            using (IDbConnection session = await DbContext.GetConnectionAsync())
            {
                return await session.InsertAsync(appointmentRegistration);
            }
        }

        public virtual async Task<int> InsertAppointmentRegistrationListAsync(List<AppointmentRegistration> customers)
        {
            if (customers != null && customers.Any())
            {
                StringBuilder builder = new StringBuilder(50);
                builder.AppendFormat("INSERT INTO `{0}`( PatientName,AppointmentTime,MedicalCondition,Deleted,CreationTime ) VALUES ( @PatientName,@AppointmentTime,@MedicalCondition,@Deleted,@CreationTime );", TableName);

                int result = await ExecuteAsync(builder.ToString(), customers);
                return result;
            }

            return 0;
        }

        public virtual async Task<bool> UpdateAppointmentRegistrationAsync(AppointmentRegistration appointmentRegistration)
        {
            if (appointmentRegistration == null)
                throw new ArgumentNullException(nameof(appointmentRegistration));

            return await UpdateAsync(appointmentRegistration);
        }

        public virtual async Task<bool> DeleteAppointmentRegistrationAsync(AppointmentRegistration appointmentRegistration)
        {
            if (appointmentRegistration == null)
                throw new ArgumentNullException(nameof(appointmentRegistration));

            return await DeleteAsync(appointmentRegistration);
        }
    }


}
